set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=90;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=90; --  每天生成 60 个分区

-- created by kumiler
-- on 2023/3/11 18:48
-- DESC 
insert overwrite table jms_dim.dim_sys_agency_district_city_base
select tmp.*, city_id
from (
         SELECT sa.id,
                sa.name,
                sa.code,
                sa.network_id,
                sa.network_name,
                sa.network_code,
                sa.is_enable,
                sa.is_delete,
                sa.create_by,
                sa.update_by,
                sa.create_by_name,
                sa.update_by_name,
                sa.create_time,
                sa.update_time,
                c.city_list
         FROM jms_ods.sys_agency_area sa
                  LEFT JOIN (SELECT sc.agency_area_id, concat_ws(",", collect_list(sc.city_id)) city_list
                             FROM jms_ods.sys_agency_area_city sc
                             where sc.dt = '{{ execution_date | cst_ds }}'
                             GROUP BY sc.agency_area_id) c ON c.agency_area_id = sa.id
         where sa.dt = '{{ execution_date | cst_ds }}'
           -- and sa.network_name = '京津冀代理区'
     ) tmp
         lateral view explode(split(city_list, ',')) product_type_tmp as city_id
